ODBC connection to a PC or SQL database

If the datasource of the subreport is a PC-type (for example, MS Access) or SQL-type (for example, MS SQL Server), and the report is connecting via ODBC (P2SODBC.DLL), you can use code similar to the following:

  1. Get the Tables collection for the subreport.
Set CRXTables = CRXSubreport.Database.Tables 
  1. Get the first table from the Tables collection.
Set CRXTable = CRXTables.Item(1) 
  1. Finally, set the location of the .mdb file.
CRXTable.SetLogOnInfo <ODBC_DSN>, <databasename>, <userid>, <password> 
Active data connection to a PC or SQL database

If the datasource of the subreport is an active data source (ex. DAO, RDO, ADO, CDO, etc.), and the report is connecting using the active data driver (P2SMON.DLL), then you can use code similar to the following:

  1. Get the Tables collection for the subreport.
Set CRXTables = CRXSubreport.Database.Tables 
  1. Get the first table from the Tables collection.
Set CRXTable = CRXTables.Item(1) 
  1. Finally, set the location of the.mdb file.
CRXTable.SetDataSource rs, 3 

There are many ways that you can access a subreport, and change the database location for a subreport. The above examples are simplified and generic so that they can be used and altered to accommodate an application with any report.

Connecting to OLEDB providers

Each OLEDB provider requires a specific connection string. The following sample connection strings are for use in the "Connection" input box of the "ADO and OLE DB" option on the "Select Data Source" dialog box for the Active Data report expert:

Microsoft Jet 3.51 OLE DB Provider

Provider=Microsoft.Jet.OLEDB.3.51;Data Source=c:\Access\Northwind.mdb

Note:    At this time, it is not possible to use secured Access databases in the Crystal Report expert.

Microsoft OLEDB Provider for SQL Server

Syntax:

Provider=SQLOLEDB;SERVER=SName;DATABASE=DBName; UID=MyID; PWD=MyPWD 

Example:

Provider=SQLOLEDB;Server=TechTest;Database=Pubs;UID=Admin;PWD=sa 

Microsoft OLEDB Provider for ODBC Drivers

Syntax:
Provider=MSDASQL;DSN=MyDSN;UID=MyUID;PWD=MyPassword 
Examples:

For secured Data Sources:

Provider=MSDASQL;DSN=Xtreme Sample Database;UID=Admin;PWD=Password 

For non-secured data sources type the DSN:

Xtreme Sample Database

Note:    If it is OLE DB that is being used as the provider for ODBC, select the first option of the "Select Data Source" dialog box ("ODBC(ADO)") which uses that specific provider by default.

Microsoft OLEDB Provider for Oracle

Syntax:

Provider=MSDAORA;Password=;User ID=;Data Source= 

Example:

Provider=MSDAORA;Password=MyPassword;User ID=Admin;Data 
Source=MyOracleServer

Note:    It will probably be necessary to re-type the UserID and Password in the corresponding text boxes of the Select Recordset dialog box.

Connecting to a secure Access session

If your reports connect to secure Microsoft Access sessions, you must provide session information at runtime using the SetSessionInfo method of the DatabaseTable object. This method accepts a user ID and password that are passed to the Access session to provide a connection to the database.

Session information must be added to your code using the SetSessionInfo method, even if you specified session information at design time. Session information specified at design time is not stored with the Report Designer Component.

Assuming all tables in your report are from the same secured Access database, you can use code similar to the following to set the Access session information:

Dim CRXReport As New CrystalReport1
Dim CRXTable as CRAXDRT.DatabaseTable
For Each CRXTable In CRXReport.Database.Tables
    CRXTable.SetSessionInfo "user", "password"
Next

Working with secure data in reports

If your report connects to a secure data source that requires log on information, you will not be able to log off of the data source until the Report object has been closed or has gone out of scope. Keep in mind that if you assign the Report object to the ReportSource property of the CRViewer object in the Crystal Reports Report Viewer, the Report object cannot be closed until you assign a new value to the Report Viewer or close the CRViewer object. Thus, you will not be able to log off of a secure data source until the report is no longer displayed in the Report Viewer.



Seagate Software IMG Holdings, Inc.
http://www.seagatesoftware.com
Support services:
http://support.seagatesoftware.com